/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.tesam.gedeon.dao.impl;

import com.tesam.gedeon.dao.TrafficTypeDao;
import com.tesam.gedeon.entity.Network;
import com.tesam.gedeon.entity.Provider;
import com.tesam.gedeon.entity.TrafficType;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import javax.sql.DataSource;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.simple.SimpleJdbcDaoSupport;
import org.springframework.stereotype.Repository;

/**
 *
 * @author Sistema
 */
@Repository
public class TrafficTypeDaoImpl extends SimpleJdbcDaoSupport implements TrafficTypeDao{
    
    @Autowired
    public TrafficTypeDaoImpl(DataSource dataSource) {
        setDataSource(dataSource);
    
}

    @Override
    public int getCountAll() {
        return getJdbcTemplate().queryForInt("select count(*) as total  from traffictype tt " +
                                             "inner join network net on net.id = tt.networkid " +
                                             "inner join provider pr on pr.id = tt.providerid " +
                                             "where tt.deleted = 0") ;
    }

    @Override
    public int getCountBySearch(TrafficType trafficType) {
        return getJdbcTemplate().queryForInt("select count(*) as total  from traffictype tt  " +
                          "inner join network net on net.id = tt.networkid  " +
                          "inner join provider pr on pr.id = tt.providerid " + 
                          "where tt.deleted = 0 and upper(tt.providername) like '%"+trafficType.getProviderName().toUpperCase()+"%' "); //To change body of generated methods, choose Tools | Templates.
    }

    @Override
    public List<TrafficType> getListAll() {
        List<TrafficType> result = new ArrayList();
        try{
            String sql = "select tt.* , net.description as networkname , pr.name as providerName from traffictype tt  " +
                          "inner join network net on net.id = tt.networkid  " +
                          "inner join provider pr on pr.id = tt.providerid " +
                          "where tt.deleted = 0 " ;
          
            
              List<Map<String, Object>> rows = getJdbcTemplate().queryForList(sql);
                for (Map row : rows) {
		TrafficType trafficType = new TrafficType();
		trafficType.setId((Integer)(row.get("id"))); 
                trafficType.setCode((String)(row.get("billingcode")));
                trafficType.setProviderName((String) (row.get("providername")));
                trafficType.setSubCode((String)(row.get("subcode")));
                trafficType.setProviderSubName((String)(row.get("providersubname")));
                
                Network network = new Network();
                network.setDescription((String)row.get("networkName"));
                network.setId((Integer)row.get("networkid")); 
                trafficType.setNetwork(network);
                
                Provider provider = new Provider();
                provider.setId((Integer)row.get("providerid"));
                provider.setName((String)row.get("providerName")); 
                trafficType.setProvider(provider);
                
		result.add(trafficType);
                }
        }catch(Exception e){
            System.out.println("getListAll TrafficType : "+e.getMessage());
        }
        return result;
    }

    @Override
    public List<TrafficType> getListByLimit(int startLimit, int endLimit, String dataToSort, String directionOfOrder) {
        List<TrafficType> result = new ArrayList();
        try{
            String sql = "select tt.* , net.description as networkname , pr.name as nameProvider from traffictype tt  " +
                          "inner join network net on net.id = tt.networkid  " +
                          "inner join provider pr on pr.id = tt.providerid " + 
                          "where tt.deleted = 0 " +
                          "order by "+dataToSort+" "+directionOfOrder+" limit "+
                           endLimit+" offset "+startLimit;
             System.out.println(sql);
            
              List<Map<String, Object>> rows = getJdbcTemplate().queryForList(sql);
                for (Map row : rows) {
		TrafficType trafficType = new TrafficType();
		trafficType.setId((Integer)(row.get("id"))); 
                trafficType.setCode((String)(row.get("billingcode")));
                trafficType.setProviderName((String) (row.get("providername")));
                trafficType.setProviderCode((String)(row.get("providercode")));
                trafficType.setSubCode((String)(row.get("subcode")));
                trafficType.setProviderSubName((String)(row.get("providersubname")));
                
                Network network = new Network();
                network.setDescription((String)row.get("networkName"));
                network.setId((Integer)row.get("networkid")); 
                trafficType.setNetwork(network);
                
                Provider provider = new Provider();
                provider.setId((Integer)row.get("providerid"));
                provider.setName((String)row.get("nameProvider")); 
                trafficType.setProvider(provider);
                
		result.add(trafficType);
                }
        }catch(Exception e){
            System.out.println("getListAll TrafficType : "+e.getMessage());
        }
        return result;
    }

    @Override
    public List<TrafficType> getListBySearch(int startLimit, int endLimit, String dataToSort, String directionOfOrder, TrafficType trafficType) {
        List<TrafficType> result = new ArrayList();
        try{
            String sql = "select tt.* , net.description as networkname , pr.name as nameProvider  from traffictype tt  " +
                          "inner join network net on net.id = tt.networkid  " +
                          "inner join provider pr on pr.id = tt.providerid " + 
                          "where tt.deleted = 0 and "
                    + "upper(trim(tt.providername) || trim(net.description)||trim(pr.name)||trim(tt.billingcode)||"
                    + "trim(tt.providername)||trim(tt.subcode)||trim(tt.providersubname)||trim(tt.providercode)) "
                    + "like '%"+trafficType.getProviderName().toUpperCase().replace(" ", "%")+"%' " +
                          "order by "+dataToSort+" "+directionOfOrder+" limit "+
                           endLimit+" offset "+startLimit;
            
            
              List<Map<String, Object>> rows = getJdbcTemplate().queryForList(sql);
                for (Map row : rows) {
		TrafficType trafficTypeE = new TrafficType();
		trafficTypeE.setId((Integer)(row.get("id"))); 
                trafficTypeE.setCode((String)(row.get("billingcode")));
                trafficTypeE.setProviderCode((String)(row.get("providercode")));
                trafficTypeE.setProviderName((String) (row.get("providername")));
                trafficTypeE.setSubCode((String)(row.get("subcode")));
                trafficTypeE.setProviderSubName((String)(row.get("providersubname")));
                
                Network network = new Network();
                network.setDescription((String)row.get("networkName"));
                network.setId((Integer)row.get("networkid")); 
                trafficTypeE.setNetwork(network);
                
                Provider provider = new Provider();
                provider.setId((Integer)row.get("providerid"));
                provider.setName((String)row.get("nameProvider")); 
                trafficTypeE.setProvider(provider);
                
		result.add(trafficTypeE);
                }
        }catch(Exception e){
            System.out.println("getListAll TrafficType : "+e.getMessage());
        }
        return result;
    }

    @Override
    public void delete(TrafficType trafficType) {
       getJdbcTemplate().update("update traffictype set deleted = 1 where id = ?",trafficType.getId());
    }

    @Override
    public int save(TrafficType trafficType) {
        int id = 0 ;
        try{
            System.out.println("insert into traffictype (createdby , datecreated , status , deleted , presence ,"
                    + "ownerid , providerid , networkid , billingcode , providercode , " +
                        "providername , subcode , providersubname )" +
                        "values("+trafficType.getCreatedBy()+" , NOW() , 1 , 0  , 0 ,"
                    + trafficType.getOwnerId()+" , "+trafficType.getProvider().getId()+" , "+trafficType.getNetwork().getId() 
                    + ","+trafficType.getCode()+" , "+trafficType.getProviderCode() 
                    + " ,"+trafficType.getProviderName()+" , "+trafficType.getSubCode()+" ,"+trafficType.getProviderSubName()+" )");
            getJdbcTemplate().update("insert into traffictype (createdby , datecreated , status , deleted , presence ,"
                    + "ownerid , providerid , networkid , billingcode , providercode , " +
                        "providername , subcode , providersubname )" +
                        "values(? , NOW() , 1 , 0  , 0 ,"
                    + "? , ? , ? ,? , ? ,"
                    + "? , ? , ? )",trafficType.getCreatedBy() , trafficType.getOwnerId() , 
                    trafficType.getProvider().getId() , trafficType.getNetwork().getId() , trafficType.getCode() , 
                    trafficType.getProviderCode() , trafficType.getProviderName() , trafficType.getSubCode() , 
                    trafficType.getProviderSubName());
            
            id = getSimpleJdbcTemplate().queryForInt("SELECT last_value FROM entity_id_seq");
            
        }catch(Exception e){
        System.out.println("save : "+e.getMessage());
        }
        return id ;
    }

    @Override
    public void update(TrafficType trafficType) {
   
        
        try{
        getJdbcTemplate().update("update traffictype set modifiedby = ? , datemodified = NOW() , providerid = ? , "
                               + "networkid = ? , billingcode = ? , providercode = ? , "
                               + "providername = ? , subcode = ? , providersubname = ? where id = ?" ,
                               trafficType.getModifiedBy() , trafficType.getProvider().getId() ,
                               trafficType.getNetwork().getId() , trafficType.getCode() , trafficType.getProviderCode() ,
                               trafficType.getProviderName() , trafficType.getSubCode() , trafficType.getProviderName() ,
                               trafficType.getId());
        }catch(Exception e){
        System.out.println("update : "+e.getMessage());
        }
        
     
    }

    @Override
    public List<TrafficType> getTrafficById(TrafficType trafficType) {
          List<TrafficType> result = new ArrayList();
        try{
            
            getJdbcTemplate().update("update traffictype set viewedtime = NOW()  where id = "+trafficType.getId());
            
            String sql = "select tt.* , net.description as networkname , pr.name as nameProvider  from traffictype tt  " +
                          "inner join network net on net.id = tt.networkid  " +
                          "inner join provider pr on pr.id = tt.providerid " + 
                          "where tt.deleted = 0 and tt.id = "+trafficType.getId() ;
            
            
              List<Map<String, Object>> rows = getJdbcTemplate().queryForList(sql);
                for (Map row : rows) {
		TrafficType trafficTypeE = new TrafficType();
		trafficTypeE.setId((Integer)(row.get("id"))); 
                trafficTypeE.setCode((String)(row.get("billingcode")));
                trafficTypeE.setProviderCode((String)(row.get("providercode"))); 
                trafficTypeE.setProviderName((String) (row.get("providername")));
                trafficTypeE.setSubCode((String)(row.get("subcode")));
                trafficTypeE.setProviderSubName((String)(row.get("providersubname")));
                trafficTypeE.setDateCreated((Timestamp)(row.get("dateCreated")));
                
                Network network = new Network();
                network.setDescription((String)row.get("networkName"));
                network.setId((Integer)row.get("networkid")); 
                trafficTypeE.setNetwork(network);
                
                Provider provider = new Provider();
                provider.setId((Integer)row.get("providerid"));
                provider.setName((String)row.get("nameProvider")); 
                trafficTypeE.setProvider(provider);
                
		result.add(trafficTypeE);
                }
        }catch(Exception e){
            System.out.println("getListAll TrafficType : "+e.getMessage());
        }
        return result;
    }
}
